package com.b2c.repository.oms;

import com.b2c.entity.result.PagingResponse;
import com.b2c.entity.*;
import com.b2c.entity.datacenter.vo.OfflineOrderListVo;
import com.b2c.entity.enums.OrderCancelStateEnums;
import com.b2c.entity.enums.OrderStateEnums;
import com.b2c.entity.enums.OrderTypeEnums;
import com.b2c.entity.erp.ErpGoodsSpecEntity;
import com.b2c.entity.ErpGoodsStockInfoEntity;
import com.b2c.entity.ErpOrderReturnEntity;
import com.b2c.entity.erp.enums.ErpOrderSourceEnum;
import com.b2c.entity.erp.vo.ExpressInfoVo;
import com.b2c.entity.mall.OrderCancelItemEntity;
import com.b2c.entity.result.EnumResultVo;
import com.b2c.entity.result.ResultVo;
import com.b2c.entity.vo.OrderVo;
import com.b2c.entity.enums.erp.EnumOrderReturnStatus;
import com.b2c.entity.enums.mall.EnumOrderSendStatus;
import com.b2c.repository.Tables;
import com.b2c.entity.vo.finance.FinanceOrderItemListVo;
import com.b2c.entity.vo.finance.FinanceOrderListVo;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;


/**
 * 描述：
 * 订单Repository
 *
 * @author qlp
 * @date 2019-01-03 2:56 PM
 */
@Repository
public class YgOrderRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    DataSourceTransactionManager dataSourceTransactionManager;
    @Autowired
    TransactionDefinition transactionDefinition;

    Logger log = LoggerFactory.getLogger(YgOrderRepository.class);


    /**
     * 查询订单总览
     *
     * @param pageIndex
     * @param pageSize
     * @param orderNum
     * @param mjMobile
     * @param startTime
     * @param endTime
     * @param salesmanMobile
     * @param state
     * @return
     */
    public PagingResponse<OrderVo> getOrders(Integer pageIndex, Integer pageSize, String orderNum, String mjMobile, Integer startTime, Integer endTime, String salesmanMobile, Integer developerId, Integer state, OrderTypeEnums orderType) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT SQL_CALC_FOUND_ROWS A.*,buyer.mobile");
        sb.append(",re.state as after_sale_state ,re.id as after_id ");
        sb.append(",salesman.nick_name salesman_name,salesman.mobile as salesman_mobile  ");
        sb.append(",IFNULL(developer.user_name,developer.nick_name) as developer_name,developer.mobile as developer_mobile  ");
        sb.append(" FROM " + Tables.Order + " A ");
        sb.append(" LEFT JOIN " + Tables.User + " buyer on buyer.id = A.user_id ");
        sb.append(" LEFT JOIN " + Tables.User + " salesman ON salesman.id=A.salesman_id ");//导购师
        sb.append(" LEFT JOIN " + Tables.User + " developer ON developer.id=A.developer_id ");//业务员
        sb.append(" LEFT JOIN " + Tables.OrderCancel + " re on re.order_id = A.id AND re.id=(SELECT id FROM " + Tables.OrderCancel + " WHERE order_id=A.id  ORDER BY id DESC LIMIT 1 )");
        sb.append(" WHERE 1=1 ");
        List<Object> params = new ArrayList<>();

//        if (orderType != null) {
//            if (orderType == OrderTypeEnums.PT) {
//                //云购订单，包含云购预售订单
//                sb.append("AND A.type = ? ");
//                params.add(orderType.getIndex());
////                params.add(OrderTypeEnums.PT_YUSHOU.getIndex());
//            } else {
//                sb.append("AND A.type = ? ");
//                params.add(orderType.getIndex());
//            }
//        }
        if (!StringUtils.isEmpty(orderNum)) {
            sb.append("AND A.order_num = ? ");
            params.add(orderNum);
        }
        //买家手机号
        if (!StringUtils.isEmpty(mjMobile)) {
            sb.append("AND A.consignee_mobile = ? ");
            params.add(mjMobile);
        }

        if (startTime > 0) {
            sb.append("AND A.create_on >= ? ");
            params.add(startTime);
        }
        if (endTime > 0) {
            sb.append("AND A.create_on < ? ");
            params.add(endTime);
        }
//        if (flag == 0) {
//            if (startTime > 0) {
//                sb.append("AND A.payment_time>=? ");
//                params.add(startTime);
//            }
//            if (endTime > 0) {
//                sb.append("AND A.payment_time<? ");
//                params.add(endTime);
//            }
//        } else {
//            sb.append(" AND A.send_status = ? ");
//            params.add(ErpOrderStatusEnums.HasOut.getIndex());
//            if (startTime > 0) {
//                sb.append("AND A.payment_result_time>=? ");
//                params.add(startTime);
//            }
//            if (endTime > 0) {
//                sb.append("AND A.payment_result_time<? ");
//                params.add(endTime);
//            }
//
//        }
//        if (!StringUtils.isEmpty(goodsName)) {
//            sb.append("AND A.goods_detail LIKE ? ");
//            params.add("%" + goodsName + "%");
//        }
        //导购师
        if (!StringUtils.isEmpty(salesmanMobile)) {
            sb.append("AND salesman.mobile = ? ");
            params.add(salesmanMobile);
        }
        //业务员师
        if (developerId!=null && developerId >0) {
            sb.append("AND A.developer_id =? ");
            params.add(developerId);
        }

        if (state >= 0) {
            sb.append("AND A.state = ? ");
            params.add(state);
        }

        sb.append("ORDER BY A.id DESC LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);
        var list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(OrderVo.class), params.toArray());
        Integer total = jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num ", Integer.class);
        list.forEach(o -> {
            o.setOrderItems(jdbcTemplate.query("select * from " + Tables.OrderItem + " where order_id=?", new BeanPropertyRowMapper<>(OrderItemEntity.class), o.getId()));
        });
        return new PagingResponse<>(pageIndex, pageSize, total, list);
    }


    /**
     * 查询总页数
     *
     * @return
     */
    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }



    /**
     * 平台订单发货
     *
     * @param company
     * @param companyCode
     * @param logisticsCode
     */
    public void updOrderSendStatus(String orderNum, String company, String companyCode, String logisticsCode) {
        try {
            var order = jdbcTemplate.queryForObject("select id,type,state from " + Tables.Order + " where order_num=?", new BeanPropertyRowMapper<>(OrdersEntity.class), orderNum);
            if ((order.getType() == OrderTypeEnums.PT.getIndex() && order.getState() == OrderStateEnums.WaitSend.getIndex()) || order.getType() == OrderTypeEnums.PiFa.getIndex() || order.getType() == OrderTypeEnums.DaiFa.getIndex()) {
                String updOrderSQL = "update " + Tables.Order + " set send_company=?,send_company_code=?,send_code=?,send_time=unix_timestamp(now()),state=?,send_status=? where id=? ";
                jdbcTemplate.update(updOrderSQL, company, companyCode, logisticsCode, OrderStateEnums.Delivered.getIndex(), EnumOrderSendStatus.Send.getIndex(), order.getId());
            }
        } catch (Exception e) {
            log.error("平台订单发货(" + orderNum + ")异常：" + e);
        }

    }


    private StringBuilder getOrderItemSQL() {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT SQL_CALC_FOUND_ROWS o.order_num,o.send_company as logisticsCompany,o.send_code as logisticsCode,o.send_status,o.create_on as orderTime,o.state as status");
        sb.append(",oi.title as goodsName,oi.spec_number,g.goods_number,oi.count as quantity,oi.discount_price as salePrice");
        sb.append(",concat('颜色:',oi.color,'尺码:',oi.size) specName ");
        sb.append(",IFNULL(u.user_name,u.nick_name) as buyerNick");//购买客户
        sb.append(",o.consignee as receiver");//收货人
        sb.append(",d.user_name as developer");//业务员
        sb.append(",o.shopId,sh.name as shopName,o.buyer_order_type as saleType ");
        sb.append(" FROM ").append(Tables.OrderItem).append(" as oi ");
        sb.append(" LEFT JOIN ").append(Tables.Order).append(" as o on o.id=oi.order_id");
        sb.append(" LEFT JOIN ").append(Tables.User).append(" as u on u.id = o.user_id");//购买者
        sb.append(" LEFT JOIN ").append(Tables.User).append(" as d on d.id = o.developer_id");//业务员
        sb.append(" LEFT JOIN ").append(Tables.GoodsSpec).append(" as gs on gs.spec_number = oi.spec_number");
        sb.append(" LEFT JOIN ").append(Tables.Goods).append(" as g on g.id = gs.goods_id");
        sb.append(" LEFT JOIN ").append(Tables.DcShop).append(" as sh on sh.id = o.shopId ");
        sb.append(" WHERE 1=1 ");
        return sb;
    }

    /**
     * 查询订单明细list（供财务使用）
     *
     * @param pageIndex
     * @param pageSize
     * @param orderNum
     * @param mjMobile
     * @param startTime
     * @param endTime
     * @param sendStatus
     * @return
     */
    @Transactional
    public PagingResponse<FinanceOrderItemListVo> getOrderItemList(Integer pageIndex, Integer pageSize, String orderNum, String mjMobile,String specNumber, Integer startTime, Integer endTime, Integer sendStatus) {
        StringBuilder sb = getOrderItemSQL();

        List<Object> params = new ArrayList<>();

        if (!StringUtils.isEmpty(orderNum)) {
            sb.append("AND o.order_num = ? ");
            params.add(orderNum);
        }

        //买家手机号
        if (!StringUtils.isEmpty(mjMobile)) {
            sb.append("AND o.consignee_mobile = ? ");
            params.add(mjMobile);
        }
        if (!StringUtils.isEmpty(specNumber)) {
            sb.append("AND oi.spec_number = ? ");
            params.add(specNumber);
        }

        if (startTime > 0) {
            sb.append("AND o.create_on >= ? ");
            params.add(startTime);
        }
        if (endTime > 0) {
            sb.append("AND o.create_on < ? ");
            params.add(endTime);
        }


        if (sendStatus >= 0) {
            sb.append("AND o.send_status = ? ");
            params.add(sendStatus);
        }

        sb.append("ORDER BY o.id DESC LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        var list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(FinanceOrderItemListVo.class), params.toArray());
        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), list);
    }

    /**
     * 查询订单明细excel（供财务使用）
     *
     * @param orderNum
     * @param mjMobile
     * @param startTime
     * @param endTime
     * @param sendStatus
     * @return
     */
    public List<FinanceOrderItemListVo> getOrderItemListForExcel(String orderNum, String mjMobile,String specNumber, Integer startTime, Integer endTime, Integer sendStatus) {
        StringBuilder sb = getOrderItemSQL();

        List<Object> params = new ArrayList<>();

        if (!StringUtils.isEmpty(orderNum)) {
            sb.append("AND o.order_num = ? ");
            params.add(orderNum);
        }

        //买家手机号
        if (!StringUtils.isEmpty(mjMobile)) {
            sb.append("AND o.consignee_mobile = ? ");
            params.add(mjMobile);
        }

        if (!StringUtils.isEmpty(specNumber)) {
            sb.append("AND oi.spec_number = ? ");
            params.add(specNumber);
        }

        if (startTime > 0) {
            sb.append("AND o.create_on >= ? ");
            params.add(startTime);
        }
        if (endTime > 0) {
            sb.append("AND o.create_on < ? ");
            params.add(endTime);
        }


        if (sendStatus >= 0) {
            sb.append("AND o.send_status = ? ");
            params.add(sendStatus);
        }

        sb.append("ORDER BY o.id DESC ");


        var list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(FinanceOrderItemListVo.class), params.toArray());
        return list;
    }

    private StringBuilder getOrderSQL() {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT SQL_CALC_FOUND_ROWS o.order_num,o.send_company as logisticsCompany,o.send_code as logisticsCode,o.send_status,o.create_on as orderTime,o.payment_result_time as payTime,o.state as status,o.send_time");
        sb.append(",(SELECT SUM(count) FROM ").append(Tables.OrderItem).append(" WHERE order_id = o.id) as totalQuantity");
        sb.append(",IFNULL(u.user_name,u.nick_name) as buyerNick,o.payment_price as totalAmount,o.freight as shippingFee,o.address as receiverAddress");//购买客户
        sb.append(",o.consignee as receiver,o.consignee_mobile as receiverMobile");//收货人
        sb.append(",IFNULL(d.user_name,d.nick_name) as developer");//业务员
        sb.append(",o.buyer_order_type as saleType,sh.name as shopName,o.shopId,o.sellerMemo ");
        sb.append(" FROM ").append(Tables.Order).append(" as o ");
        sb.append(" LEFT JOIN ").append(Tables.User).append(" as u on u.id = o.user_id");//购买者
        sb.append(" LEFT JOIN ").append(Tables.User).append(" as d on d.id = o.developer_id");//业务员
        sb.append(" LEFT JOIN ").append(Tables.DcShop).append(" as sh on sh.id = o.shopId ");
        sb.append(" WHERE 1=1 ");
        return sb;
    }

    /**
     * 查询订单（供财务使用）
     *
     * @param pageIndex
     * @param pageSize
     * @param orderNum
     * @param mjMobile
     * @param
     * @param startTime
     * @param endTime
     * @param sendStatus
     * @return
     */
    public PagingResponse<FinanceOrderListVo> getOrderList(Integer pageIndex, Integer pageSize, String orderNum, String mjMobile, Integer developerId, Integer startTime, Integer endTime, Integer sendStatus) {
        StringBuilder sb = getOrderSQL();

        List<Object> params = new ArrayList<>();

        if (!StringUtils.isEmpty(orderNum)) {
            sb.append("AND o.order_num = ? ");
            params.add(orderNum);
        }

        //买家手机号
        if (!StringUtils.isEmpty(mjMobile)) {
            sb.append("AND o.consignee_mobile = ? ");
            params.add(mjMobile);
        }
        //业务员
        if(developerId!=null&& developerId>0){
            sb.append(" AND o.developer_id = ? ");
            params.add(developerId);
        }

        if (startTime > 0) {
            sb.append("AND o.create_on >= ? ");
            params.add(startTime);
        }
        if (endTime > 0) {
            sb.append("AND o.create_on < ? ");
            params.add(endTime);
        }
        if (sendStatus != null && sendStatus >= 0) {
            sb.append("AND o.send_status = ? ");
            params.add(sendStatus);
        }

        sb.append("ORDER BY o.id DESC LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        var list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(FinanceOrderListVo.class), params.toArray());
        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), list);
    }

    public List<FinanceOrderListVo> getOrderListForExcel(String orderNum, String mjMobile,Integer developerId, Integer startTime, Integer endTime, Integer sendStatus) {
        StringBuilder sb = getOrderSQL();

        List<Object> params = new ArrayList<>();

        if (!StringUtils.isEmpty(orderNum)) {
            sb.append("AND o.order_num = ? ");
            params.add(orderNum);
        }

        //买家手机号
        if (!StringUtils.isEmpty(mjMobile)) {
            sb.append("AND o.consignee_mobile = ? ");
            params.add(mjMobile);
        }
        //业务员
        if(developerId!=null&& developerId>0){
            sb.append(" AND o.developer_id = ? ");
            params.add(developerId);
        }

        if (startTime > 0) {
            sb.append("AND o.create_on >= ? ");
            params.add(startTime);
        }
        if (endTime > 0) {
            sb.append("AND o.create_on < ? ");
            params.add(endTime);
        }
        if (sendStatus != null && sendStatus >= 0) {
            sb.append("AND o.send_status = ? ");
            params.add(sendStatus);
        }

        sb.append("ORDER BY o.id DESC ");


        var list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(FinanceOrderListVo.class), params.toArray());
        return list;
    }


    /**
     * 获取订单实体
     * @param orderId
     * @return
     */
    public OrdersEntity getOrderEntity(Long orderId) {
        try {
            return jdbcTemplate.queryForObject("SELECT * FROM "+Tables.Order +" WHERE id=?",new BeanPropertyRowMapper<>(OrdersEntity.class),orderId);
        }catch (Exception e){
            return null;
        }
    }

    /**
     * 获取订单items
     * @param orderId
     * @return
     */
    public List<OrderItemEntity> getOrderItems(Long orderId) {
        return jdbcTemplate.query("SELECT * FROM "+Tables.OrderItem +" WHERE order_id=?",new BeanPropertyRowMapper<>(OrderItemEntity.class),orderId);
    }

    /**
     * 获取订单详情，包括orderitems
     *
     * @param orderId
     * @return
     */
    public OfflineOrderListVo getOrderDetail(Long orderId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT  A.*,IFNULL(B.id,0) userId,B.mobile myMobile FROM " + Tables.Order + " A  LEFT JOIN " + Tables.User + " B ON B.id=A.user_id  ");
        sb.append(" WHERE A.id=? ");
        List<OfflineOrderListVo> lists = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(OfflineOrderListVo.class), orderId);
        if (lists != null && lists.size() > 0) {
            OfflineOrderListVo order = lists.get(0);
            //查询订单items
            String itemSQL = "select O.*,(SELECT number from erp_goods WHERE id=(SELECT goodsId FROM erp_goods_spec WHERE specNumber=O.spec_number)) goodsNumber," +
                    "(SELECT  concat(color_value,'(',size_value,')') FROM erp_goods_spec WHERE id=O.new_spec_id) newSpec," +
                    "(SELECT concat(color_value,'(',size_value,')') FROM erp_goods_spec  WHERE specNumber=O.spec_number) erpSpec " +
                    "from order_item O where O.order_id=?";

            var items = jdbcTemplate.query(itemSQL, new BeanPropertyRowMapper<>(OrderItemEntity.class), orderId);

            order.setItems(items);
            /****查询订单item对应的仓库库存****/
            for (var item : order.getItems()) {
                try {
                    String specNumber = StringUtils.isEmpty(item.getNewSpecNumber()) == false ? item.getNewSpecNumber() : item.getSpecNumber();

                    var erpGoodsSpec = jdbcTemplate.queryForObject("select * from " + Tables.ErpGoodsSpec + " WHERE specNumber=?", new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), specNumber);
                    var sku = jdbcTemplate.queryForObject("select SUM(currentQty) currentQty,SUM(lockedQty) lockedQty from " + Tables.ErpGoodsStockInfo + "  WHERE specId=? AND isDelete = 0", new BeanPropertyRowMapper<>(ErpGoodsStockInfoEntity.class), erpGoodsSpec.getId());
                    Long djhCount = jdbcTemplate.queryForObject(" SELECT IFNULL((select SUM(quantity) from erp_order_item WHERE skuId=? AND (STATUS=0 OR STATUS=1) ),0) djhCount", Long.class, erpGoodsSpec.getId());
                    item.setCurrentQty(Optional.ofNullable(sku.getCurrentQty()).orElse(0L));
                    item.setLockedQty(Optional.ofNullable(sku.getLockedQty()).orElse(0L));
                    item.setPickQty(djhCount.longValue());

                } catch (Exception e2) {
                    item.setCurrentQty(0l);
                    item.setLockedQty(0l);
                    item.setPickQty(0l);
                }

            }


            order.setTotalCount(jdbcTemplate.queryForObject("SELECT  IFNULL(SUM(count),0) FROM " + Tables.OrderItem + " WHERE  order_id=?", Integer.class, orderId));
            return order;
        } else return null;
    }


    /**
     * 订单售后状态修改
     *
     * @param id
     * @param state
     * @param createBy
     * @param comment
     */
    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Integer> reviewRefund(Long id, Integer state, String createBy, String comment) {
        //查询退款单
        String sql = "SELECT * FROM " + Tables.OrderCancel + " WHERE id=?";
        OrderCancelEntity refundOrder = null;
        try {
            refundOrder = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(OrderCancelEntity.class), id);
        } catch (Exception e1) {
            return new ResultVo<>(EnumResultVo.NotFound, "退货订单不存在");//订单不存在return 404;
        }
        OrdersEntity order = null;
        try {
            //查询关联的订单
            order = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.Order + " WHERE id=?", new BeanPropertyRowMapper<>(OrdersEntity.class), refundOrder.getOrderId());
        } catch (Exception e) {
            return new ResultVo<>(EnumResultVo.NotFound, "订单不存在");//订单不存在return 404;
        }

        //订单来源
        String orderSource = ErpOrderSourceEnum.YUNGOU.getIndex();
        Integer shopId = 3;

//        if (order.getType().intValue() == OrderTypeEnums.PT.getIndex() || order.getType().intValue() == OrderTypeEnums.PT_YUSHOU.getIndex()) {
//            //预售订单、平台订单，来源都是云购
//            orderSource = ErpOrderSourceEnum.YUNGOU.getIndex();
//        } else if (order.getType().intValue() == OrderTypeEnums.PiFa.getIndex()) {
//            //采购订单，来源是下单系统
//            orderSource = ErpOrderSourceEnum.OFFLINE.getIndex();
//        } else if (order.getType().intValue() == OrderTypeEnums.DaiFa.getIndex()) {
//            orderSource = ErpOrderSourceEnum.DaiFa.getIndex();
//        } else {
//            orderSource = "Unknown";
//        }

        var returnOrderItem = jdbcTemplate.query("SELECT * FROM " + Tables.OrderCancelItem + " WHERE order_cancel_id=?", new BeanPropertyRowMapper<>(OrderCancelItemEntity.class), refundOrder.getId());
        if (returnOrderItem == null || returnOrderItem.size() == 0)
            return new ResultVo<>(EnumResultVo.NotFound, "退货订单明细不存在");

        if (state.intValue() == OrderCancelStateEnums.Agree.getIndex()) {

            if(refundOrder.getType() != 0 ) return new ResultVo<>(EnumResultVo.DataError, "不是退货订单，请到云购后台操作");

            //同意退款
            if (refundOrder.getState() == OrderCancelStateEnums.RefundApply.getIndex()) {
                //更新退货订单信息 order_cancel
                jdbcTemplate.update("UPDATE " + Tables.OrderCancel + " SET state=? WHERE id =?", state, id);

                jdbcTemplate.update("UPDATE " + Tables.OrderCancelItem + " SET status=? WHERE order_cancel_id =?", state, refundOrder.getId());


                /**********循环更新退货商品信息 ***********/
                for (var ritem : returnOrderItem) {
                    //更新退货商品信息
                    jdbcTemplate.update("UPDATE " + Tables.OrderItem + " SET after_sale_state=? WHERE id =?", state, ritem.getOrderItemId());
                }


                /***********  查询仓库系统退货订单 是否存在 *************/
                var erpOrderReturn = jdbcTemplate.query("SELECT * FROM " + Tables.ErpOrderReturn + " WHERE order_num=? AND shopId=?", new BeanPropertyRowMapper<>(ErpOrderReturnEntity.class), refundOrder.getOrderCancelNum(), shopId);
                if (erpOrderReturn != null && erpOrderReturn.size() > 0) {
                    //退货单已经存在
                    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                    return new ResultVo<>(EnumResultVo.Fail, "仓库已经存在该退货单");
                }

                /***********  插入仓库系统退款订单 erp_order_return    erp_order_return_item  *************/
                try {
                    //插入仓库退货订单 erp_order_return
                    String returnOrder = "INSERT INTO " + Tables.ErpOrderReturn +
                            " (order_num,orderTime,createTime,contactPerson,mobile,address,source,status,source_order_num,shopId)" +
                            " VALUE (?,?,?,?,?,?,?,?,?,?)";

                    KeyHolder keyHolder = new GeneratedKeyHolder();

                    OrdersEntity finalOrder = order;
                    OrderCancelEntity finalRefundOrder = refundOrder;
//                    String finalOrderSource = orderSource;
                    jdbcTemplate.update(new PreparedStatementCreator() {
                        @Override
                        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                            PreparedStatement ps = connection.prepareStatement(returnOrder, Statement.RETURN_GENERATED_KEYS);
                            ps.setString(1, finalRefundOrder.getOrderCancelNum());
                            ps.setLong(2, finalRefundOrder.getCreateOn());
                            ps.setLong(3, System.currentTimeMillis() / 1000);
                            ps.setString(4, finalOrder.getConsignee());
                            ps.setString(5, finalOrder.getConsigneeMobile());
                            ps.setString(6, finalOrder.getAddress());
                            ps.setString(7, orderSource);
                            ps.setInt(8, EnumOrderReturnStatus.WaitSend.getIndex());
                            ps.setString(9, finalOrder.getOrderNum());
                            ps.setInt(10, shopId);
                            return ps;
                        }
                    }, keyHolder);
                    Long returnOrderId = keyHolder.getKey().longValue();

                    //插入仓库退货订单明细 erp_order_return_item
                    String returnOrderItemSQL = "INSERT INTO " + Tables.ErpOrderReturnItem + " (orderId,goodsId,skuId,skuNumber,quantity,receiveType,createTime,status,inQuantity,refundAmount) VALUE (?,?,?,?,?,?,?,?,?,?)";

                    for (var ritem : returnOrderItem) {
                        //查询仓库系统sku
                        var sku = jdbcTemplate.queryForObject("select * from " + Tables.ErpGoodsSpec + " WHERE specNumber=?", new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), ritem.getSpecNumber());
                        double refundAmount = ritem.getQuantity() * ritem.getDiscountPrice().doubleValue();
                        //更新退货商品信息
//                        jdbcTemplate.update("UPDATE " + Tables.OrderItem + " SET after_sale_state=? WHERE id =?", state, ritem.getOrderItemId());
                        jdbcTemplate.update(returnOrderItemSQL, returnOrderId, sku.getGoodsId(), sku.getId(), sku.getSpecNumber(), ritem.getQuantity(), 0, System.currentTimeMillis() / 1000, 0, 0,refundAmount);
                    }
                } catch (Exception e) {
                    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                    return new ResultVo<>(EnumResultVo.Fail, "同意退货处理失败"+e.getMessage());
                }

            } else return new ResultVo<>(EnumResultVo.StateError, "退货单状态不一致，无法操作");//return 505;//退货单状态不一致，无法操作

        } else if (state.intValue() == OrderCancelStateEnums.Refuse.getIndex()) {
            if(refundOrder.getType() != 0 ) return new ResultVo<>(EnumResultVo.DataError, "不是退货订单，请到云购后台操作");
            //拒绝退货
            if (refundOrder.getState() == OrderCancelStateEnums.RefundApply.getIndex() ) {
                //更新退货订单信息 order_cancel
                jdbcTemplate.update("UPDATE " + Tables.OrderCancel + " SET state=? WHERE id =?", state, id);
                jdbcTemplate.update("UPDATE " + Tables.OrderCancelItem + " SET status=? WHERE order_cancel_id =?", state, refundOrder.getId());

                /**********循环更新退货商品信息 ***********/
                for (var ritem : returnOrderItem) {
                    //更新退货商品信息、已退款数量
                    jdbcTemplate.update("UPDATE " + Tables.OrderItem + " SET after_sale_state=?,returnedCount=returnedCount-? WHERE id =?", state, ritem.getQuantity(), ritem.getOrderItemId());
                }

            } else new ResultVo<>(EnumResultVo.StateError, "退货单状态不一致，无法操作");//return 505;//退货单状态不一致，无法操作

        } else if (state.intValue() == OrderCancelStateEnums.Received.getIndex()) {
            //后台确认收货
            if (refundOrder.getState() == OrderCancelStateEnums.Delivered.getIndex()) {

                /************查询仓库是否收到货物************/
                try {
//                    var erpOrderReturn = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpOrderReturn + " WHERE order_num=?", new BeanPropertyRowMapper<>(ErpOrderReturnEntity.class), refundOrder.getOrderCancelNum());
//                    if (erpOrderReturn.getStatus().intValue() == 1) {
//                        //更新退货订单信息 order_cancel
//                        jdbcTemplate.update("UPDATE " + Tables.OrderCancel + " SET state=? WHERE id =?", state, id);
//
//                        jdbcTemplate.update("UPDATE " + Tables.OrderCancelItem + " SET status=? WHERE order_cancel_id =?", state, refundOrder.getId());
//
//                        /**********循环更新退货商品信息 ***********/
//                        for (var ritem : returnOrderItem) {
//                            //更新退货商品信息
//                            jdbcTemplate.update("UPDATE " + Tables.OrderItem + " SET after_sale_state=? WHERE id =?", state, ritem.getOrderItemId());
//                        }
//
//                    } else
//                        return new ResultVo<>(EnumResultVo.DataError, "仓库还没收到货");

                } catch (Exception e) {
                    return new ResultVo<>(EnumResultVo.DataError, "仓库系统退货订单不存在，请联系工程师修复BUG");
                }


            } else return new ResultVo<>(EnumResultVo.StateError, "退货单未发货，无法操作");//return 506;//退货单未发货

        } else if (state.intValue() == OrderCancelStateEnums.Paying.getIndex()) {
            //开始打款(退货退款)
            if (refundOrder.getState() == OrderCancelStateEnums.Received.getIndex() && refundOrder.getType() == 0) {
                //更新退货订单信息 order_cancel
                jdbcTemplate.update("UPDATE " + Tables.OrderCancel + " SET state=? WHERE id =?", OrderCancelStateEnums.Success.getIndex(), id);
                jdbcTemplate.update("UPDATE " + Tables.OrderCancelItem + " SET status=? WHERE order_cancel_id =?", OrderCancelStateEnums.Success.getIndex(), refundOrder.getId());
                /**********循环更新退货商品信息 ***********/
                for (var ritem : returnOrderItem) {
                    //更新退货商品信息
                    jdbcTemplate.update("UPDATE " + Tables.OrderItem + " SET after_sale_state=? WHERE id =?", OrderCancelStateEnums.Success.getIndex(), ritem.getOrderItemId());
                }

            }
            //未发款(仅退款)
            if (refundOrder.getType() == 1) {
                jdbcTemplate.update("UPDATE " + Tables.OrderCancel + " SET state=? WHERE id =?", state, id);
                jdbcTemplate.update("UPDATE " + Tables.OrderCancelItem + " SET status=? WHERE order_cancel_id =?", state, refundOrder.getId());
                //更新订单商品售后状态
                jdbcTemplate.update("UPDATE " + Tables.OrderItem + " SET after_sale_state=? WHERE order_id =?", state, refundOrder.getOrderId());
                //更新订单状态
                jdbcTemplate.update("update " + Tables.Order + " set state=? where id=? ", OrderStateEnums.FAIL.getIndex(), refundOrder.getOrderId());
            }
        } else {
            return new ResultVo<>(EnumResultVo.Fail);
        }
//        TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
        return new ResultVo<>(EnumResultVo.SUCCESS);//不支持的操作
//            //添加订单日志
//            String insert = "INSERT " + Tables.OrderLogs + " (order_id,type,comment,create_on,create_by) VALUES (?,2,?,?,?)";
//            jdbcTemplate.update(insert, id, "退货完成" + comment, System.currentTimeMillis() / 1000, createBy);

    }

    /**
     * 采购订单售后
     *
     * @param id
     * @param state
     */
    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Integer> reviewRefundOffline(Long id, Integer state, ExpressInfoVo exress) {
        //查询退款单
        String sql = "SELECT * FROM " + Tables.OrderCancel + " WHERE id=?";
        OrderCancelEntity refundOrder = null;
        try {
            refundOrder = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(OrderCancelEntity.class), id);
        } catch (Exception e1) {
            return new ResultVo<>(EnumResultVo.NotFound, "退货订单不存在");//订单不存在return 404;
        }
        OrdersEntity order = null;
        try {
            //查询关联的订单
            order = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.Order + " WHERE id=?", new BeanPropertyRowMapper<>(OrdersEntity.class), refundOrder.getOrderId());
        } catch (Exception e) {
            return new ResultVo<>(EnumResultVo.NotFound, "订单不存在");//订单不存在return 404;
        }
        //订单来源
        String orderSource = ErpOrderSourceEnum.YUNGOU.getIndex();
//        Integer shopId = 3;
        if (order.getType().intValue() == OrderTypeEnums.PT.getIndex() ) {
            //预售订单、平台订单，来源都是云购
            orderSource = ErpOrderSourceEnum.YUNGOU.getIndex();
        } else if (order.getType().intValue() == OrderTypeEnums.PiFa.getIndex()) {
            //采购订单，来源是下单系统
            orderSource = ErpOrderSourceEnum.OFFLINE.getIndex();
        } else if (order.getType().intValue() == OrderTypeEnums.DaiFa.getIndex()) {
            orderSource = ErpOrderSourceEnum.DaiFa.getIndex();
        } else {
            orderSource = "Unknown";
        }

        var returnOrderItem = jdbcTemplate.query("SELECT * FROM " + Tables.OrderCancelItem + " WHERE order_cancel_id=?", new BeanPropertyRowMapper<>(OrderCancelItemEntity.class), refundOrder.getId());
        if (returnOrderItem == null || returnOrderItem.size() == 0)
            return new ResultVo<>(EnumResultVo.NotFound, "退货订单明细不存在");


        /**1.同意退货**/
        if (state.intValue() == OrderCancelStateEnums.Delivered.getIndex() && refundOrder.getState() == OrderCancelStateEnums.RefundApply.getIndex()) {

            //更新退货订单信息 order_cancel
            jdbcTemplate.update("UPDATE " + Tables.OrderCancel + " SET send_company=?,send_company_code=?,send_code=?,state=? WHERE id =?", exress.getLogisticsCompany(), exress.getLogisticsCompanyCode(), exress.getLogisticsCode(), state, id);

            jdbcTemplate.update("UPDATE " + Tables.OrderCancelItem + " SET status=? WHERE order_cancel_id =?", state, refundOrder.getId());

            /**********循环更新退货商品信息 ***********/
            for (var ritem : returnOrderItem) {
                //更新退货商品信息
                jdbcTemplate.update("UPDATE " + Tables.OrderItem + " SET after_sale_state=? WHERE id =?", state, ritem.getOrderItemId());
            }

            /***********  查询仓库系统退货订单 是否存在 *************/
            var erpOrderReturn = jdbcTemplate.query("SELECT * FROM " + Tables.ErpOrderReturn + " WHERE order_num=? AND shopId=?", new BeanPropertyRowMapper<>(ErpOrderReturnEntity.class), refundOrder.getOrderCancelNum(), order.getShopId());
            if (erpOrderReturn != null && erpOrderReturn.size() > 0) {
                //退货单已经存在
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                return new ResultVo<>(EnumResultVo.Fail, "仓库已经存在该退货单");
            }

            /***********  插入仓库系统退款订单 erp_order_return    erp_order_return_item  *************/
            try {
                //插入仓库退货订单 erp_order_return
                String returnOrder = "INSERT INTO " + Tables.ErpOrderReturn +
                        " (order_num,orderTime,createTime,contactPerson,mobile,address,source,status,logisticsCompany,logisticsCompanyCode,logisticsCode,source_order_num,shopId)" +
                        " VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?)";

                KeyHolder keyHolder = new GeneratedKeyHolder();

                OrdersEntity finalOrder = order;

                OrderCancelEntity finalRefundOrder = refundOrder;
                String finalOrderSource = orderSource;
                jdbcTemplate.update(new PreparedStatementCreator() {
                    @Override
                    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                        PreparedStatement ps = connection.prepareStatement(returnOrder, Statement.RETURN_GENERATED_KEYS);
                        ps.setString(1, finalRefundOrder.getOrderCancelNum());
                        ps.setLong(2, finalRefundOrder.getCreateOn());
                        ps.setLong(3, System.currentTimeMillis() / 1000);
                        ps.setString(4, finalOrder.getConsignee());
                        ps.setString(5, finalOrder.getConsigneeMobile());
                        ps.setString(6, exress != null ? exress.getAddress() : finalOrder.getAddress());
                        ps.setString(7, finalOrderSource);
                        ps.setInt(8, EnumOrderReturnStatus.WaitReceive.getIndex());
                        ps.setString(9, exress != null ? exress.getLogisticsCompany() : "");
                        ps.setString(10, exress != null ? exress.getLogisticsCompanyCode() : "");
                        ps.setString(11, exress != null ? exress.getLogisticsCode() : "");
                        ps.setString(12, finalOrder.getOrderNum());
                        ps.setLong(13, finalOrder.getShopId());
                        return ps;
                    }
                }, keyHolder);
                Long returnOrderId = keyHolder.getKey().longValue();

                //插入仓库退货订单明细 erp_order_return_item
                String returnOrderItemSQL = "INSERT INTO " + Tables.ErpOrderReturnItem + " (orderId,goodsId,skuId,skuNumber,quantity,receiveType,createTime,status,inQuantity,badQuantity,refundAmount) VALUE (?,?,?,?,?,?,?,?,?,?,?)";

                for (var ritem : returnOrderItem) {
                    //查询仓库系统sku
                    var sku = jdbcTemplate.queryForObject("select * from " + Tables.ErpGoodsSpec + " WHERE specNumber=?", new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), ritem.getSpecNumber());
                    double refundAmount = ritem.getQuantity() * ritem.getDiscountPrice().doubleValue();
                    //更新退货商品信息
                    jdbcTemplate.update(returnOrderItemSQL, returnOrderId, sku.getGoodsId(), sku.getId(), sku.getSpecNumber(), ritem.getQuantity(), 0, System.currentTimeMillis() / 1000, 0, 0, 0,refundAmount);
                }
            } catch (Exception e) {
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                return new ResultVo<>(EnumResultVo.Fail, "同意退货处理失败"+e.getMessage());
            }
        }

        /**2.拒绝退货**/
        if (state.intValue() == OrderCancelStateEnums.Refuse.getIndex() && refundOrder.getState() == OrderCancelStateEnums.RefundApply.getIndex()) {
            //拒绝退货
            if (refundOrder.getType() == 0) {
                //更新退货订单信息 order_cancel
                jdbcTemplate.update("UPDATE " + Tables.OrderCancel + " SET state=? WHERE id =?", state, id);

                jdbcTemplate.update("UPDATE " + Tables.OrderCancelItem + " SET status=? WHERE order_cancel_id =?", state, refundOrder.getId());
                /**********循环更新退货商品信息 ***********/
                for (var ritem : returnOrderItem) {
                    //更新退货商品信息
                    jdbcTemplate.update("UPDATE " + Tables.OrderItem + " SET after_sale_state=? WHERE id =?", state, ritem.getOrderItemId());
                }

            } else if (refundOrder.getType() == 1) {
                //更新退货订单信息
                jdbcTemplate.update("UPDATE " + Tables.OrderCancel + " SET state=? WHERE id =?", state, id);
                //更新订单订单待发货
                jdbcTemplate.update("UPDATE " + Tables.Order + " SET state=? WHERE id =?", 2, refundOrder.getOrderId());
            }

        }
        //eTransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
        return new ResultVo<>(EnumResultVo.SUCCESS, "成功");
    }
}
